﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_Rights_GetRightsToClass_V2]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_Rights_GetRightsToClass_V2];
GO
CREATE PROCEDURE [dbo].[sproc_Rights_GetRightsToClass_V2]
    @UserName nvarchar(50),
    @ClassID  int
AS
BEGIN
SET NOCOUNT ON

DECLARE @actid INT;
DECLARE @ReturnValue INT;
DECLARE @userId int;
DECLARE @rightTable TABLE (Act_ID INT);

-- Get the id of the user
SELECT @userId = Staff_ID FROM dbo.UDS_Staff WHERE Staff_Name = @Username;
IF ( @@ROWCOUNT <> 1 )
BEGIN
    RAISERROR(N'The specified user "%s" does not exist.', 16, 1, @Username);
    RETURN -1;
END

IF @ClassID > 0 
BEGIN
    -- Get a list of permissions owned by the user.
    INSERT INTO @rightTable (Act_ID)
        SELECT Proc_ID
        FROM dbo.UDS_Proc
        WHERE 1 = [dbo].[fn_has_access_permission](@ClassID, @userId, Proc_ID);

    SELECT P.*
        FROM
            dbo.UDS_Proc P
            INNER JOIN @rightTable R ON P.Proc_ID = R.Act_ID
        WHERE
            P.Proc_ID IN (
                -- Filter the permission by UDS_Class.ClassType
                SELECT a.Proc_ID
                    FROM 
                        dbo.UDS_Proc_Type a
                        INNER JOIN dbo.UDS_Class b ON a.Proc_Type = b.ClassType
                    WHERE
                        b.classid = @classID
            )
        ORDER BY Proc_ID;
END
ELSE
BEGIN
    INSERT INTO @rightTable
        SELECT DISTINCT A.act_id 
                FROM 
                    dbo.UDS_Assign_Rule a,
                    dbo.uds_staff_in_position b
                WHERE
                    a.position_id = b.position_id 
                    and a.team_id = 0
                    and a.based_on = 1 /* position */
                    and b.staff_id = @userId;

    INSERT INTO @rightTable
        SELECT DISTINCT A.act_id 
                FROM 
                    dbo.UDS_Assign_Rule a ,
                    dbo.uds_staff_in_role b
                WHERE
                    a.role_id = b.role_id 
                    and a.team_id = 0
                    and a.based_on = 3 /* role */
                    and b.staff_id = @userId;

    INSERT INTO @rightTable
        SELECT DISTINCT A.act_id 
                FROM 
                    dbo.UDS_Assign_Rule a 
                WHERE
                    a.team_id = 0
                    and a.based_on = 4 /* staff */
                    and a.staff_id = @userId;

    SELECT p.*
        FROM
            dbo.uds_proc p,
            @rightTable r
        WHERE p.proc_id = r.act_id
        ORDER BY p.proc_id;
END

END
GO